//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package com.aidong.dao.wills;

import com.aidong.dao.base.ConditionMap;
import com.aidong.dao.base.OrderSet;
import com.aidong.dao.wills.BaseDao;
import com.aidong.model.wills.ContractInfo;
import com.aidong.model.wills.PayList;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.springframework.stereotype.Repository;

@Repository
public class PayListDao extends BaseDao<PayList, Integer> {
	public PayListDao() {
	}

	public List<PayList> getListBySaleId(int saleId, String saleType) {
		return this.queryList("saleId=:p1 and saleType=:p2", new Object[]{Integer.valueOf(saleId), saleType}, (new OrderSet()).asc("autoId"), -1);
	}

	public List<PayList> getSumBySaleType(ConditionMap cd) {
		ArrayList params = new ArrayList();
		String sqlw = cd.toSql(params);
		String sql = "select saleType, sum(amount) amount from PayList\n" + (sqlw.length() > 0?"where " + sqlw:"") + " group by saleType order by saleType";
		return this.queryListBySql(PayList.class, sql, params.toArray());
	}

	public List<PayList> getSumByPayType(ConditionMap cd) {
		ArrayList params = new ArrayList();
		String sqlw = cd.toSql(params);
		String sql = "select payType, sum(amount) amount from PayList\n" + (sqlw.length() > 0?"where " + sqlw:"") + " group by payType order by payType";
		return this.queryListBySql(PayList.class, sql, params.toArray());
	}

	public List<PayList> getSumBySaleTypeChangTai(String shopCode, Date dateFrom, Date dateTo) {
		String sql = "select \'会籍\' as saleType, isnull(sum(amount),0) as amount, count(distinct saleid) saleId from paylist where saleType=\'01\' and shopcode=:p1 and saleid in (select saleId from MemberContract where saledate>=:p2 and saledate<=:p3  and cardtype not in (select code from cardtypecode where chName like \'%全市通%\') and ( (valids=1 and validunit=\'04\') or (valids=2 and validunit=\'04\') or (valids<=24 and validunit=\'03\') or (valids<=105 and validunit=\'02\') )) union all select \'私教\' as saleType, isnull(sum(amount),0) as amount, count(distinct saleid) from paylist where saleType=\'02\' and shopcode=:p4 and saleid in (select saleId from PtContract where saledate>=:p5 and saledate<=:p6 and SaleClassify<>\'3\' and payAmount<=11000 and ptcode in (select code from PtItemCode where chName like \'%常规%\')) union all select \'小商品\' as saleType, isnull(sum(amount),0) as amount, count(distinct saleid) from paylist where saleType=\'03\' and shopcode=:p7 and paytype not in (\'S014\') and saledate>=:p8 and saledate<=:p9";
		return this.queryListBySql(PayList.class, sql, new Object[]{shopCode, dateFrom, dateTo, shopCode, dateFrom, dateTo, shopCode, dateFrom, dateTo});
	}

	public List<PayList> getSumBySaleTypeWanXiang(String shopCode, Date dateFrom, Date dateTo) {
		String sql = "select case saletype when 1 then \'会籍\' when 2 then \'私教\' when 3 then \'小商品\' end saletype, sum(amount1+amount2+amount3) amount, count(*) saleId from (" + this.getWanXiangSql() + ") t where amount1+amount2+amount3<>0 group by saletype order by saletype";
		return this.queryListBySql(PayList.class, sql, new Object[]{shopCode, dateFrom, dateTo, shopCode, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, shopCode, dateFrom, dateTo});
	}

	public List<ContractInfo> getListBySaleTypeWanXiang(String shopCode, Date dateFrom, Date dateTo) {
		String sql = this.getWanXiangSql();
		return this.queryListBySql(ContractInfo.class, sql, new Object[]{shopCode, dateFrom, dateTo, shopCode, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, shopCode, dateFrom, dateTo});
	}

	public List<PayList> getSumBySaleTypeSH061(String shopCode, Date dateFrom, Date dateTo) {
		String sql = "select case saletype when 1 then \'会籍\' when 2 then \'私教\' when 3 then \'小商品\' end saletype, sum(amount1+amount2+amount3) amount, count(*) saleId from (" + this.getSH061Sql() + ") t where amount1+amount2+amount3<>0 group by saletype order by saletype";
		return this.queryListBySql(PayList.class, sql, new Object[]{shopCode, dateFrom, dateTo, shopCode, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, shopCode, dateFrom, dateTo});
	}

	public List<ContractInfo> getListBySaleTypeSH061(String shopCode, Date dateFrom, Date dateTo) {
		String sql = this.getSH061Sql();
		return this.queryListBySql(ContractInfo.class, sql, new Object[]{shopCode, dateFrom, dateTo, shopCode, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, shopCode, dateFrom, dateTo});
	}

	public List<PayList> getSumBySaleTypeSH063(String shopCode, Date dateFrom, Date dateTo) {
		String sql = "select case saletype when 1 then \'会籍\' when 2 then \'私教\' when 3 then \'小商品\' end saletype, sum(amount1+amount2+amount3) amount, count(*) saleId from (" + this.getSH063Sql() + ") t where amount1+amount2+amount3<>0 group by saletype order by saletype";
		return this.queryListBySql(PayList.class, sql, new Object[]{shopCode, dateFrom, dateTo, shopCode, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, shopCode, dateFrom, dateTo});
	}

	public List<ContractInfo> getListBySaleTypeSH063(String shopCode, Date dateFrom, Date dateTo) {
		String sql = this.getSH063Sql();
		return this.queryListBySql(ContractInfo.class, sql, new Object[]{shopCode, dateFrom, dateTo, shopCode, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, shopCode, dateFrom, dateTo});
	}

	public List<PayList> getSumBySaleTypeBJ004(String shopCode, Date dateFrom, Date dateTo) {
		String sql = "select case saletype when 1 then \'会籍\' when 2 then \'私教\' when 3 then \'小商品\' end saletype, sum(amount1+amount2+amount3) amount, count(*) saleId from (" + this.getBJ004Sql() + ") t where amount1+amount2+amount3<>0 group by saletype order by saletype";
		return this.queryListBySql(PayList.class, sql, new Object[]{shopCode, dateFrom, dateTo, shopCode, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, shopCode, dateFrom, dateTo});
	}

	public List<ContractInfo> getListBySaleTypeBJ004(String shopCode, Date dateFrom, Date dateTo) {
		String sql = this.getBJ004Sql();
		return this.queryListBySql(ContractInfo.class, sql, new Object[]{shopCode, dateFrom, dateTo, shopCode, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, dateFrom, dateTo, shopCode, dateFrom, dateTo});
	}

	private String getWanXiangSql() {
		String sql = "select 1 saleType, saleid, saledate, payamount\n  , (select isnull(sum(amount),0) from paylist where saleType=\'01\' and paytype=\'S001\' and saleid=mc.saleid) amount1\n  , (select isnull(sum(amount),0) from paylist where saleType=\'01\' and paytype=\'S002\' and saleid=mc.saleid) amount2\n  , (select isnull(sum(amount),0) from paylist where saleType=\'01\' and paytype not in (\'S001\',\'S002\',\'S014\') and saleid=mc.saleid) amount3\nfrom membercontract mc where shopcode=:p1 and saledate>=:p2 and saledate<=:p3\n  and ((valids=1 and validunit=\'04\') or (valids=2 and validunit=\'04\') or (valids<=24 and validunit=\'03\') or (valids<=105 and validunit=\'02\'))\nunion all\nselect 2 saleType, saleid, saledate, payamount\n  , (select isnull(sum(amount),0) from paylist where saleType=\'02\' and paytype=\'S001\' and saleid=pc.saleid)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'02\' and paytype=\'S002\' and saleid=pc.saleid)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'02\' and paytype not in (\'S001\',\'S002\',\'S014\') and saleid=pc.saleid)\nfrom ptcontract pc where shopcode=:p4 and saledate>=:p5 and saledate<=:p6\n  and ptcode in (select code from PtItemCode where chName like \'%常规%\')\nunion all\nselect 3 saleType, autoid, saledate, saleamount\n  , (select isnull(sum(amount),0) from paylist where saleType=\'03\' and paytype=\'S001\' and saleid=ps.autoid and saledate>=:p7 and saledate<=:p8)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'03\' and paytype=\'S002\' and saleid=ps.autoid and saledate>=:p9 and saledate<=:p10)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'03\' and paytype not in (\'S001\',\'S002\',\'S014\') and saleid=ps.autoid and saledate>=:p11 and saledate<=:p12)\nfrom pssproductsale ps where shopcode=:p13 and saledate>=:p14 and saledate<=:p15";
		return sql;
	}

	private String getSH061Sql() {
		String sql = "select 1 saleType, saleid, saledate, payamount\n  , (select isnull(sum(amount),0) from paylist where saleType=\'01\' and paytype=\'S001\' and saleid=mc.saleid) amount1\n  , (select isnull(sum(amount),0) from paylist where saleType=\'01\' and paytype=\'S002\' and saleid=mc.saleid) amount2\n  , (select isnull(sum(amount),0) from paylist where saleType=\'01\' and paytype not in (\'S001\',\'S002\',\'S014\') and saleid=mc.saleid) amount3\nfrom membercontract mc where shopcode=:p1 and saledate>=:p2 and saledate<=:p3\n  and ((validunit=\'04\' and valids<=2) or (valids<=24 and validunit=\'03\') or (valids<=105 and validunit=\'02\'))\n  and cardtype not in (select code from cardtypecode where chName like \'%全市通%\')\nunion all\nselect 2 saleType, saleid, saledate, payamount\n  , (select isnull(sum(amount),0) from paylist where saleType=\'02\' and paytype=\'S001\' and saleid=pc.saleid)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'02\' and paytype=\'S002\' and saleid=pc.saleid)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'02\' and paytype not in (\'S001\',\'S002\',\'S014\') and saleid=pc.saleid)\nfrom ptcontract pc where shopcode=:p4 and saledate>=:p5 and saledate<=:p6 and payAmount<=11000\n  and ptcode in (select code from PtItemCode where chName like \'%常规%\')\nunion all\nselect 3 saleType, autoid, saledate, saleamount\n  , (select isnull(sum(amount),0) from paylist where saleType=\'03\' and paytype=\'S001\' and saleid=ps.autoid and saledate>=:p7 and saledate<=:p8)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'03\' and paytype=\'S002\' and saleid=ps.autoid and saledate>=:p9 and saledate<=:p10)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'03\' and paytype not in (\'S001\',\'S002\',\'S014\') and saleid=ps.autoid and saledate>=:p11 and saledate<=:p12)\nfrom pssproductsale ps where shopcode=:p13 and saledate>=:p14 and saledate<=:p15";
		return sql;
	}

	private String getSH063Sql() {
		String sql = "select 1 saleType, saleid, saledate, payamount\n  , (select isnull(sum(amount),0) from paylist where saleType=\'01\' and paytype=\'S001\' and saleid=mc.saleid) amount1\n  , (select isnull(sum(amount),0) from paylist where saleType=\'01\' and paytype=\'S002\' and saleid=mc.saleid) amount2\n  , (select isnull(sum(amount),0) from paylist where saleType=\'01\' and paytype not in (\'S001\',\'S002\',\'S014\') and saleid=mc.saleid) amount3\nfrom membercontract mc where shopcode=:p1 and saledate>=:p2 and saledate<=:p3\n  and ((validunit=\'04\' and valids<=4) or (valids<=48 and validunit=\'03\') or (valids<=210 and validunit=\'02\'))\nunion all\nselect 2 saleType, saleid, saledate, payamount\n  , (select isnull(sum(amount),0) from paylist where saleType=\'02\' and paytype=\'S001\' and saleid=pc.saleid)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'02\' and paytype=\'S002\' and saleid=pc.saleid)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'02\' and paytype not in (\'S001\',\'S002\',\'S014\') and saleid=pc.saleid)\nfrom ptcontract pc where shopcode=:p4 and saledate>=:p5 and saledate<=:p6 and payAmount<=11000\n  and ptcode in (select code from PtItemCode where chName like \'%常规%\')\nunion all\nselect 3 saleType, autoid, saledate, saleamount\n  , (select isnull(sum(amount),0) from paylist where saleType=\'03\' and paytype=\'S001\' and saleid=ps.autoid and saledate>=:p7 and saledate<=:p8)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'03\' and paytype=\'S002\' and saleid=ps.autoid and saledate>=:p9 and saledate<=:p10)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'03\' and paytype not in (\'S001\',\'S002\',\'S014\') and saleid=ps.autoid and saledate>=:p11 and saledate<=:p12)\nfrom pssproductsale ps where shopcode=:p13 and saledate>=:p14 and saledate<=:p15";
		return sql;
	}

	private String getBJ004Sql() {
		String sql = "select 1 saleType, saleid, saledate, payamount\n  , (select isnull(sum(amount),0) from paylist where saleType=\'01\' and paytype=\'S001\' and saleid=mc.saleid) amount1\n  , (select isnull(sum(amount),0) from paylist where saleType=\'01\' and paytype=\'S002\' and saleid=mc.saleid) amount2\n  , (select isnull(sum(amount),0) from paylist where saleType=\'01\' and paytype not in (\'S001\',\'S002\',\'S014\') and saleid=mc.saleid) amount3\nfrom membercontract mc where shopcode=:p1 and saledate>=:p2 and saledate<=:p3\n  and ((validunit=\'04\' and valids<=4) or (valids<=48 and validunit=\'03\') or (valids<=210 and validunit=\'02\'))\n  and cardtype not in (select code from cardtypecode where chName like \'%全市通%\')\nunion all\nselect 2 saleType, saleid, saledate, payamount\n  , (select isnull(sum(amount),0) from paylist where saleType=\'02\' and paytype=\'S001\' and saleid=pc.saleid)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'02\' and paytype=\'S002\' and saleid=pc.saleid)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'02\' and paytype not in (\'S001\',\'S002\',\'S014\') and saleid=pc.saleid)\nfrom ptcontract pc where shopcode=:p4 and saledate>=:p5 and saledate<=:p6 and payAmount<=11000\n  and ptcode in (select code from PtItemCode where chName like \'%常规%\')\nunion all\nselect 3 saleType, autoid, saledate, saleamount\n  , (select isnull(sum(amount),0) from paylist where saleType=\'03\' and paytype=\'S001\' and saleid=ps.autoid and saledate>=:p7 and saledate<=:p8)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'03\' and paytype=\'S002\' and saleid=ps.autoid and saledate>=:p9 and saledate<=:p10)\n  , (select isnull(sum(amount),0) from paylist where saleType=\'03\' and paytype not in (\'S001\',\'S002\',\'S014\') and saleid=ps.autoid and saledate>=:p11 and saledate<=:p12)\nfrom pssproductsale ps where shopcode=:p13 and saledate>=:p14 and saledate<=:p15";
		return sql;
	}
}
